﻿/*
 * GdoSqlConnection.SpatialSchema
 * 
 * initial implementation : 20.9.2008
 *
 * Copyright (c) Adam Sida, asida@centrum.cz.
 * All rights reserved.
 * 
 * for license see Petra.Gis.License.txt
 */
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Data.SqlClient;
using Petra.Gis.DataServices;

namespace Petra.Gis.DataServices.GDO.Sqlserver
{
  public partial class GdoSqlConnection : SpatialDbConnection
  {


    /// <summary>
    /// returns following data table structure:
    /// FEATURE_CATALOG,FEATURE_SCHEMA, FEATURE_NAME, FEATURE_TYPE, TITLE, 
    /// ABSTRACT, AUTHORITY, BOUNDS, CONTACTINFO, KEYWORDS
    /// </summary>
    protected override DataTable getFeaturesInfo(string[] rowFilterArray)
    {

      string filter = "";

      if (rowFilterArray != null)
        filter = createFeatureInfoFilter(rowFilterArray);
      
      //DataTable tab = new DataTable("Features");
      //DataColumn col;
      //col = new DataColumn();
      //col.DataType = Type.GetType("System.String");
      //col.ColumnName = "id";
      //tab.Columns.Add(col);


      // create DataReader and process SQL query that must return this:
      // Catalog, Schema, Name, Title, Abstract, Authority, Bounds, ContactInfo, Keywords

      string cmdSql = @"
--Catalog, Schema, Name, Type, Title, Abstract, Authority, Bounds, ContactInfo, Keywords
select * from 
(select t.TABLE_CATALOG as FEATURE_CATALOG,
t.TABLE_SCHEMA as FEATURE_SCHEMA,
t.TABLE_NAME as FEATURE_NAME,
t.TABLE_TYPE,
t.TABLE_NAME as TITLE,
IsNull(f.FeatureDescription,'') as ABSTRACT,
IsNull(f.FeatureDescription,'') as AUTHORITY,
IsNull(f.FeatureDescription,'') as BOUNDS,
IsNull(f.FeatureDescription,'') as CONTACTINFO,
IsNull(f.FeatureDescription,'') as KEYWORDS
from INFORMATION_SCHEMA.TABLES t
join  Gfeatures f
on t.TABLE_NAME = f.FeatureName) t";

      if (filter != "")
        cmdSql += " WHERE " + filter;

      SqlCommand cmd = _sqlConnection.CreateCommand();
      cmd.CommandType = CommandType.Text;
      cmd.CommandText = cmdSql;

      SqlDataAdapter oDA = new SqlDataAdapter();
      oDA.SelectCommand = cmd;

      DataSet oDS = new DataSet("FeatureBaseInfo"); //any name

      oDA.Fill(oDS, "Features");

      return oDS.Tables["Features"];

    }




    /// <summary>
    /// returns following data table structure:
    /// FEATURE_CATALOG, FEATURE_SCHEMA, FEATURE_NAME, FIELD_NAME, TITLE, 
    /// DOMAIN_GEOMETRY_TYPE, COORDINATESYSTEM_ID.
    /// where COORDINATESYSTEM_ID is data source depended (non standard) value
    /// see table COORDINATE_SYSTEMS
    /// </summary>
    protected override DataTable getFieldsInfo(string[] rowFilterArray)
    {

      string filter = "";

      if (rowFilterArray != null)
        filter = createFieldsInfoFilter(rowFilterArray);


      string cmdSql = @"
--FEATURE_CATALOG, FEATURE_SCHEMA, FEATURE_NAME, FIELD_NAME, TITLE, 
--DOMAIN_GEOMETRY_TYPE, COORDINATESYSTEM_ID
select * from (
select c.TABLE_CATALOG as FEATURE_CATALOG,
c.TABLE_SCHEMA as FEATURE_SCHEMA,
c.TABLE_NAME as FEATURE_NAME,
c.COLUMN_NAME as FIELD_NAME,
IsNull(g.FieldDescription,'') as TITLE,
g.GeometryType DOMAIN_GEOMETRY_TYPE,
g.GCoordSystemGuid as COORDINATESYSTEM_ID
from INFORMATION_SCHEMA.COLUMNS c
join FieldLookup f
on f.FeatureName = c.TABLE_NAME and
f.FieldName = c.COLUMN_NAME
join GeometryProperties g
on f.IndexID = g.IndexID) t";

      if (filter != "")
        cmdSql += " WHERE " + filter;

      SqlCommand cmd = _sqlConnection.CreateCommand();
      cmd.CommandType = CommandType.Text;
      cmd.CommandText = cmdSql;

      SqlDataAdapter oDA = new SqlDataAdapter();
      oDA.SelectCommand = cmd;

      DataSet oDS = new DataSet("FeatureFieldsBaseInfo"); //any name

      oDA.Fill(oDS, "Fields");

      return oDS.Tables["Fields"];

    }





    /// <summary>
    /// returns following data table structure:
    /// COORDINATESYSTEM_ID, COORDINATESYSTEM_INFO 
    /// where COORDINATESYSTEM_ID is data source depended (non standard) value
    /// </summary>
    protected override DataTable getCsInfo(string[] rowFilterArray)
    {
      
      string filter = "";

      if (rowFilterArray != null)
        filter = createCoordSystemInfoFilter(rowFilterArray);

      string cmdSql = @"
--COORDINATESYSTEM_ID, COORDINATESYSTEM_INFO
select * from (select CSGUID as COORDINATESYSTEM_ID,
name + ' ' + description as COORDINATESYSTEM_INFO
from GCoordSystem) t";

      if (filter != "")
        cmdSql += " WHERE " + filter;

      SqlCommand cmd = _sqlConnection.CreateCommand();
      cmd.CommandType = CommandType.Text;
      cmd.CommandText = cmdSql;

      SqlDataAdapter oDA = new SqlDataAdapter();
      oDA.SelectCommand = cmd;

      DataSet oDS = new DataSet("FeatureCSBaseInfo"); //any name

      oDA.Fill(oDS, "CoordinateSystems");

      return oDS.Tables["CoordinateSystems"];

    }




    /// <summary>
    /// returns following data table structure:
    /// FEATURE_CATALOG,FEATURE_SCHEMA, FEATURE_NAME, FIELD_NAME, SPATIAL_INDEX_TYPE
    /// where spatial index type is string with value (none, gdosql_bounds, gdosql_tile)
    /// </summary>
    protected override DataTable getSpatialIndexInfo(string[] rowFilterArray)
    {
      string filter = "";

      if (rowFilterArray != null)
        filter = createSpatialIndexInfoFilter(rowFilterArray);


      string cmdSql = @"
select * from (
select TABLE_CATALOG as FEATURE_CATALOG,
TABLE_SCHEMA as FEATURE_SCHEMA,
TABLE_NAME as FEATURE_NAME,
COLUMN_NAME,
case
when exists(select * from GTileIndexes where TABLE_SCHEMA = t.TABLE_SCHEMA and TABLE_NAME = t.TABLE_NAME)
then '"+SpatialIndexType.TileIndex+@"' 
when
exists(select * from GTileIndexes where TABLE_SCHEMA = t.TABLE_SCHEMA and TABLE_NAME = t.TABLE_NAME) and
exists(select * from INFORMATION_SCHEMA.COLUMNS where TABLE_SCHEMA = t.TABLE_SCHEMA and
   TABLE_NAME = t.TABLE_NAME and
   t.COLUMN_NAME + '_xlo' = COLUMN_NAME and
   t.COLUMN_NAME + '_xhi' = COLUMN_NAME and
   t.COLUMN_NAME + '_ylo' = COLUMN_NAME and
   t.COLUMN_NAME + '_yhi' = COLUMN_NAME
   )
then '"+SpatialIndexType.TileAndBoundsIndex+@"' 
when 
exists(select * from INFORMATION_SCHEMA.COLUMNS where TABLE_SCHEMA = t.TABLE_SCHEMA and
   TABLE_NAME = t.TABLE_NAME and
   t.COLUMN_NAME + '_xlo' = COLUMN_NAME or
   t.COLUMN_NAME + '_xhi' = COLUMN_NAME or
   t.COLUMN_NAME + '_ylo' = COLUMN_NAME or
   t.COLUMN_NAME + '_yhi' = COLUMN_NAME
   )
then 'BoundsIndex'"+SpatialIndexType.BoundingBoxIndex+@"' 
end
as SPATIAL_INDEX_TYPE
from
(
select c.TABLE_CATALOG, c.TABLE_SCHEMA, c.TABLE_NAME, c.COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS c
join GFieldMapping g on
g.TABLE_NAME = c.TABLE_NAME and
g.COLUMN_NAME = c.COLUMN_NAME and
g.DATA_SUBTYPE > 0
) t
where t.TABLE_CATALOG = db_name()
) t
";

      if (filter != "")
        cmdSql += " WHERE " + filter;

      SqlCommand cmd = _sqlConnection.CreateCommand();
      cmd.CommandType = CommandType.Text;
      cmd.CommandText = cmdSql;

      SqlDataAdapter oDA = new SqlDataAdapter();
      oDA.SelectCommand = cmd;

      DataSet oDS = new DataSet("FeatureSpatialIndexesInfo"); //any name

      oDA.Fill(oDS, "SpatialIndexes");

      return oDS.Tables["SpatialIndexes"];
    }




    /// <summary>
    /// returns following data table structure:
    /// FEATURE_CATALOG,FEATURE_SCHEMA, FEATURE_NAME, FIELD_NAME, TILE_FIELD_NAME, TILE_FN_NAME
    /// where spatial index type is string with value (none, gdosql_bounds, gdosql_tile)
    /// </summary>
    protected override DataTable getTileSpatialIndexInfo(string[] rowFilterArray)
    {

      string filter = "";

      if (rowFilterArray != null)
        filter = createTileSpatialIndexInfoFilter(rowFilterArray);

      string cmdSql = @"
select * from (
select c.TABLE_CATALOG as FEATURE_CATALOG,
gt.TABLE_SCHEMA as FEATURE_SCHEMA,
gt.TABLE_NAME as FEATURE_NAME,
gt.GEOMETRY_COLUMN_NAME as FIELD_NAME,
gt.TILE_COLUMN_NAME as TILE_FIELD_NAME,
gt.TILE_TABLE_NAME as TILE_FN_NAME,
gt.FILTER_OPTIONS
from GTileIndexes gt join
INFORMATION_SCHEMA.COLUMNS c
on
gt.TABLE_SCHEMA = c.TABLE_SCHEMA and 
gt.TABLE_NAME = c.TABLE_NAME and
gt.GEOMETRY_COLUMN_NAME = c.COLUMN_NAME
where c.TABLE_CATALOG = db_name()
) t
";

      if (filter != "")
        cmdSql += " WHERE " + filter;

      SqlCommand cmd = _sqlConnection.CreateCommand();
      cmd.CommandType = CommandType.Text;
      cmd.CommandText = cmdSql;

      SqlDataAdapter oDA = new SqlDataAdapter();
      oDA.SelectCommand = cmd;

      DataSet oDS = new DataSet("FeatureSpatialTileIndexesInfo"); //any name

      oDA.Fill(oDS, "SpatialTileIndexesInfo");

      return oDS.Tables["SpatialTileIndexesInfo"];

    }




    protected override DataTable getQtreeSpatialIndexInfo(string[] rowFilterArray)
    {
      throw new NotSupportedException();
    }







  }
}
